#!/usr/bin/python
# -*- coding: utf-8 -*-
# AUTHOR:   yemd
# FILENAME: py_mysql.py
# DESCRIBE: mysql数据存储封装库
# CREATED:  2014-01-21 16:06
# MODIFIED: 2014-01-21 16:06

import sys
import copy
import inspect
import time
import MySQLdb
from dm_py_util import *

RETRY_TIMEOUT_FACTOR = 5
RECONNECT_TIME = 30

READ_QUERY_SQL_KEY = 'select' #查询SQL语句标识#

##########################################
#
#封装mysql管理库
#
##########################################
class PY_Mysql_Manager():
    def __init__(self):
        print self.__class__.__name__
        self._writer = PY_Mysql_Case()
        self._readers = []
        self._cluster = PY_Cluster_Stat_Manager()
        self._retry_connect_times = 0       #reader_server重连上限
        self._reader_use_times = 0          #reader_server使用次数
        self._cn_ = self.__class__.__name__ #classname,for log debug

    def print_server(self):
        _fn_ = inspect.currentframe().f_code.co_name
        try:
            DEBUG_LOG(_fn_,"_reader_use_times",str(self._reader_use_times))
            DEBUG_LOG(_fn_,"_retry_connect_times",str(self._retry_connect_times))

            if self._writer:
                DEBUG_LOG(_fn_,"-----------------writer_server_print----------------")
                DEBUG_LOG(_fn_,"host-db",self._writer._host,self._writer._db)
                DEBUG_LOG(_fn_,"user-passwd",self._writer._user,self._writer._passwd)
                DEBUG_LOG(_fn_,"timeout-mark",str(self._writer._timeout),str(self._writer._mark))
                DEBUG_LOG(_fn_,"readonly",str(self._writer._readonly))
                DEBUG_LOG(_fn_,"broken",str(self._writer._broken))
                DEBUG_LOG(_fn_,"last_active_time",str(self._writer._last_active_time))

            for server in self._readers:
                DEBUG_LOG(_fn_,"-----------------reader_server_print----------------")
                if server is None:
                    ERROR_LOG(_fn_,"server is None")
                    continue
                DEBUG_LOG(_fn_,"host-db",server._host,server._db)
                DEBUG_LOG(_fn_,"user-passwd",server._user,server._passwd)
                DEBUG_LOG(_fn_,"timeout-mark",str(server._timeout),str(server._mark))
                DEBUG_LOG(_fn_,"readonly",str(server._readonly))
                DEBUG_LOG(_fn_,"broken",str(server._broken))
                DEBUG_LOG(_fn_,"last_active_time",str(server._last_active_time))
            #打印服务器使用状态
            self._cluster.print_list()
        except Exception,e:
            ERROR_LOG(self._cn_,_fn_,":error",str(e))


    '''
    ##
    # @brief init 失败抛出DM_InputError
    #
    # @param config 配置文件
    #
    # @return  None
    '''
    def init(self,config):
        _fn_ = inspect.currentframe().f_code.co_name
        try:
            cfgparser = PYC_ConfigParser(config)
            writetimeout = int(cfgparser.get_config("sqlconfig","writetimeout"))
            readtimeout = int(cfgparser.get_config("sqlconfig","readtimeout"))
            readernum = int(cfgparser.get_config("sqlconfig","readernum"))
            DEBUG_LOG(_fn_,"readernum",str(readernum))
            #writer_server config_init
            if self._init_mysql_server(cfgparser,"sql_writer",self._writer,False)<0:
                ERROR_LOG(_fn_,":_writer error",config)
                raise DM_InputError(_fn_,str(e))
            self._writer.set_master()
            self._writer.set_timeout(writetimeout)
            #reader_server config_init
            for i in range(0,readernum):
                reader = PY_Mysql_Case()
                seckey = "sql_reader_%d"%(i)
                if self._init_mysql_server(cfgparser,seckey,reader,True)<0:
                    ERROR_LOG(_fn_,":reader error",config);
                    raise DM_InputError(_fn_,str(e))
                reader.set_timeout(readtimeout)
                if reader._mark > 0:
                    self._readers.append(reader)
            if not len(self._readers):
                ERROR_LOG(_fn_,"no reader server can use")
                raise DM_InputError(_fn_,str(e))
            #设置down服务器重连上限:即reids使用次数一旦未达到重连上限,执行up_servers#
            self._retry_connect_times = RETRY_TIMEOUT_FACTOR * (readernum - 1)
            #连接所有数据库服务器#
            return self._allconnect()
        except Exception,e:
            ERROR_LOG(self._cn_,_fn_,":error",str(e))
            raise DM_InputError(_fn_,str(e))

    #
    #获取mysql_server的基础配置信息
    #
    def _init_mysql_server(self,cfgparser,section,server,read):
        _fn_ = inspect.currentframe().f_code.co_name
        try:
            DEBUG_LOG(_fn_,"start")
            server._host = cfgparser.get_config(section,"address")
            server._port = int(cfgparser.get_config(section,"port"))
            server._user = cfgparser.get_config(section,"user")
            server._passwd = cfgparser.get_config(section,"password")
            server._db = cfgparser.get_config(section,"database")
            if server._port <= 0:
                ERROR_LOG(_fn_,"port<=0,config errror")
                return -1
            DEBUG_LOG(_fn_,"host-db",server._host,server._db)
            if read:
                server._mark = int(cfgparser.get_config(section,"mark"))
                DEBUG_LOG(_fn_,"reader.mark",str(server._mark))
            return 0
        except Exception,e:
            ERROR_LOG(self._cn_,_fn_,":error",str(e))
            return -1

    def _allconnect(self):
        _fn_ = inspect.currentframe().f_code.co_name
        try:
            DEBUG_LOG(_fn_,"connect-writer-------------------------------------");
            if self._writer.connect() < 0:
                ERROR_LOG(_fn_,"writer server connect error")
                return -1
            DEBUG_LOG(_fn_,"readers servernum",str(len(self._readers)))
            for i in range(0,len(self._readers)):
                DEBUG_LOG(_fn_,"connect-reader-----------------------------------");
                DEBUG_LOG(_fn_,"host-db",self._readers[i]._host,self._readers[i]._db)
                if self._readers[i].connect() < 0:
                    ERROR_LOG(_fn_,"down a reader server:severnum",str(i))
                    self._cluster.add_d_server(i,self._readers[i]._mark)
                else:
                    self._cluster.add_u_server(i,self._readers[i]._mark)

            if self._cluster.get_down_num() == len(self._readers):
                ERROR_LOG(_fn_,"all reader server connect error!")
                return -1
            self._cluster.print_list() #server data print test
            DEBUG_LOG("-------------------all_init_finished--------------------------")
            return 0
        except Exception,e:
            ERROR_LOG(self._cn_,_fn_,":error",str(e))
            return -1

    #
    # 尝试恢复所有down掉的服务器
    #
    def _up_servers(self):
        _fn_ = inspect.currentframe().f_code.co_name
        try:
            DEBUG_LOG(_fn_,"up_servers--------------------------------------")
            downnum = self._cluster.get_down_num()
            DEBUG_LOG(_fn_,"server downnum",str(downnum))
            for i in range(0,downnum):
                selnum = self._cluster.get_d_first()
                if selnum < 0:
                    DEBUG_LOG(_fn_,"no down server")
                    return
                if self._readers[selnum].connect() < 0:
                    ERROR_LOG(_fn_,"reconnect server error:host-db",
                                        self._readers[selnum]._host,
                                        self._readers[selnum]._db)
                    self._cluster.ignore_d_first()
                else:
                    DEBUG_LOG(_fn_,"connect suc:start up_d_first",str(selnum))
                    self._cluster.up_d_first()
        except Exception,e:
            ERROR_LOG(self._cn_,_fn_,":error",str(e))
            return

    #
    # 选择一个可用的读服务器
    # 每次选择后都要查看是否有down服务器,有则需要增加_reader_use_times次数,
    # 并且每隔retry_connect_times重试次数之后尝试恢复所有down服务器
    # 目的：尽量保证随时有可用服务器,进行可读服务器的重连机制
    #
    def _select_reader(self):
        _fn_ = inspect.currentframe().f_code.co_name
        try:
            DEBUG_LOG(_fn_,"start........................")
            retry = False
            selnum = -1
            while True:
                selnum = self._cluster.select_num()
                self._cluster.print_list()
                if selnum < 0:
                    if retry:
                        break
                    retry = True
                    self._up_servers()
                    self._reader_use_times = 0
                else:
                    DEBUG_LOG(_fn_,"suc get a server:selnum",str(selnum))
                    retry = False
                if not retry:
                    break
            if selnum < 0:
                ERROR_LOG(_fn_,"server are all down!!cannot restart")
                return None
            #有关down服务器恢复的工作:使用次数达到重试次数时恢复所有down服务器#
            downnum = self._cluster.get_down_num()
            if downnum > 0:
                self._reader_use_times = self._reader_use_times + 1 #使用次数+1
            if downnum and self._reader_use_times >= self._retry_connect_times:
                DEBUG_LOG(_fn_,"downnum-use_times",str(downnum),str(self._reader_use_times))
                self._up_servers()
                self._reader_use_times = 0
            DEBUG_LOG(_fn_,"_reader_use_times",str(self._reader_use_times))
            return self._readers[selnum]
        except Exception,e:
            ERROR_LOG(self._cn_,_fn_,":error",str(e))
            return None

    #
    #挂掉某个服务器:仅将broken字段设为True,redis上下文不置空
    #
    def _down_server(self,server):
        _fn_ = inspect.currentframe().f_code.co_name
        try:
            DEBUG_LOG(_fn_,"start!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!")
            server.set_broken()
            if server.is_readonly():
                DEBUG_LOG(_fn_,"reader server::start down_u_first")
                self._cluster.down_u_first()
            DEBUG_LOG(_fn_,"only set broken",server._host,server._db)
        except Exception,e:
            ERROR_LOG(self._cn_,_fn_,":error",str(e))

    #
    #获取server读写实例：True(写服务器), False(读服务器)
    #
    def get_db_case(self,write):
        _fn_ = inspect.currentframe().f_code.co_name
        try:
            DEBUG_LOG(_fn_,"start------------------------------------")
            DEBUG_LOG(_fn_,"write",str(write))
            server = PY_Mysql_Case()
            if write:
                server = self._writer
            else:
                server = self._select_reader()
            if server is None:
                ERROR_LOG(_fn_,"server is None,write",str(write))
                return None
            #判断server可用性
            #重连机制：server已经挂了 或者 超过活跃时间
            #          注意重连时,确保redis连接上下文是ping通的
            activetime = time.time() - server._last_active_time
            if server._broken or activetime >= RECONNECT_TIME:
                DEBUG_LOG(_fn_,"reconnect:broken",str(server._broken))
                DEBUG_LOG(_fn_,"reconnect:active-max",str(activetime),str(RECONNECT_TIME))
                if server.connect()<0:
                    self._down_server(server)
                    ERROR_LOG(_fn_,"active server to reconnect error")
                    return None
            self.print_case(server)
            if server.ping() < 0:
                return None
            return server
        except Exception,e:
            ERROR_LOG(self._cn_,_fn_,":error",str(e))
            return None

    def print_case(self,server):
        _fn_ = inspect.currentframe().f_code.co_name
        DEBUG_LOG(_fn_,"-----------------print_server_case----------------")
        DEBUG_LOG(_fn_,"host-db",self._writer._host,self._writer._db)
        DEBUG_LOG(_fn_,"timeout-mark",str(self._writer._timeout),str(self._writer._mark))
        DEBUG_LOG(_fn_,"readonly",str(self._writer._readonly))
        DEBUG_LOG(_fn_,"broken",str(self._writer._broken))
        DEBUG_LOG(_fn_,"last_active_time",str(self._writer._last_active_time))



##########################################
#
# 封装mysql服务器连接实例
#
##########################################
class PY_Mysql_Case():
    def __init__(self,Host=None,User='root',Passwd='root',Db='mysql',Timeout=-1,Charset='utf8',Port=3306,Mark=-1):
        self._host = Host
        self._user = User
        self._passwd = Passwd
        self._db = Db
        self._timeout = Timeout
        self._charset = Charset
        self._port = Port
        self._mark = Mark              #服务器mark值
        self._readonly = True          #服务器是否只读
        self._broken = True            #服务器是否挂了
        self._mysqlcontext = None      #redis连接句柄
        self._last_active_time = 0.0
        self._cn_ = self.__class__.__name__
        #self._connect()

    def set_timeout(self,timeout):
        self._timeout = timeout

    def set_master(self):
        self._readonly = False

    def set_broken(self):
        self._broken = True

    def is_readonly(self):
        return self._readonly

    def active(self):
        self._last_active_time = time.time()

    def connect(self):
        _fn_ = inspect.currentframe().f_code.co_name
        try:
            #释放server的所有上下文
            self._release()
            #构建server连接上下文
            self._mysqlcontext = MySQLdb.connect(host = self._host,
                                               port = self._port,
                                               user = self._user,
                                               passwd = self._passwd,
                                               charset = self._charset,
                                               db = self._db,
                                               connect_timeout = self._timeout)
            print self._mysqlcontext
            #设置自动提交#
            self._mysqlcontext.autocommit(1)
            #用ping查看是否能成功连接服务器
            """
            if self._ping() < 0:
                ERROR_LOG(_fn_,":can't ping self",self._host,str(self._port))
                return -1
            """
            self._broken = False
            self.active()
            return 0
        except Exception,e:
            self._mysqlcontext = None
            self._broken = True
            ERROR_LOG(_fn_,"host-broken",self._host,str(self._broken))
            ERROR_LOG(self._cn_,_fn_,":error",str(e))
            return -1

    def _release(self):
        _fn_ = inspect.currentframe().f_code.co_name
        try:
            DEBUG_LOG(_fn_,"start!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!")
            if self._mysqlcontext:
                DEBUG_LOG(_fn_,":mysqlcontext exist, set it None!!!!!!!!!!")
                self._mysqlcontext.close()
                self._mysqlcontext = None
            self._broken = True
            self._last_active_time = 0.0
        except Exception,e:
            ERROR_LOG(self._cn_,_fn_,":error",str(e))

    def ping(self):
        _fn_ = inspect.currentframe().f_code.co_name
        try:
            if self._mysqlcontext is None:
                ERROR_LOG("mysqlcontext is None",self._host)
                return -1
            self._mysqlcontext.ping()
            DEBUG_LOG(_fn_,":ping server success",self._host)
            return 0
        except Exception,e:
            ERROR_LOG(self._cn_,_fn_,":error",str(e))
            return -1

    #
    # 查询接口,只接受select开头的读语句,写语句使用execute()函数执行
    # select 开头的语句默认为查询语句，正确返回查询结果，
    # 写操作返回最后一次插入的id
    #
    def query(self,sql_query,need_last_id=True):
        _fn_ = inspect.currentframe().f_code.co_name
        DEBUG_LOG(_fn_,"start+++++++++++++++++++++++++++++++++")
        if self._mysqlcontext is None:
            ERROR_LOG(_fn_,":mysqlcontext is None")
            raise DM_MysqlError('mysqlcontext is None', _fn_)
        if not sql_query:
            ERROR_LOG(_fn_,"sqlquery is None")
            raise DM_MysqlError('sql query is None', _fn_)
            #校验select语句判断读/写查询方式#
        try:
            l_sql_query = sql_query.strip().lower()
            if l_sql_query.startswith(READ_QUERY_SQL_KEY):
                DEBUG_LOG(_fn_,"read sql query start!!!!")
                (retnum,result) = self._read_query(l_sql_query)
            else:
                DEBUG_LOG(_fn_,"write sql query start!!!!")
                (retnum,result) = self._write_query(sql_query,need_last_id)
        except Exception,e:
            ERROR_LOG(self._cn_,_fn_,":error",str(e))
            raise DM_MysqlError('sql _query filed', str(e), _fn_)
        return result

    def _back(self,Ret=-1,Result=[],Last_id=-1):
        back = {}
        back["ret"] = Ret
        back["result"] = list(Result)
        back["last_id"] = Last_id
        return back

    def _read_query(self,sql_query):
        _fn_ = inspect.currentframe().f_code.co_name
        #try:
        DEBUG_LOG(_fn_,"execute_sql",sql_query)
        #执行查询#
        self._mysqlcontext.query(sql_query)
        result = self._mysqlcontext.store_result()
        self._mysqlcontext.commit()
        if not result:
            if self._mysqlcontext.field_count():
                ERROR_LOG(_fn_,"query error: field count is not 0 but return 0 rows")
                self._mysqlcontext = None
                return (-1,[])
        row_num = result.num_rows()
        backrows = result.fetch_row(row_num,1)
        DEBUG_LOG(_fn_,"sql read rows_num",str(row_num))
        #print backrows,type(backrows)
        #更新server active_time
        self.active()
        DEBUG_LOG(_fn_,":success query!!!!")
        return (0,backrows)
        #except Exception,e:
        #    ERROR_LOG(self._cn_,_fn_,":error",str(e))
        #    raise DM_MysqlError('sql _read_query filed', str(e), _fn_)


    #
    # 写入接口,除了select语句不接受外,写语句皆通过此函数执行
    # 返回(retnum,last_id),其中last_id为插入的自增id值
    # retnum:
    #    0: suc   1:no affect    错误抛出异常
    #
    def _write_query(self,sql_query,need_last_id=False):
        _fn_ = inspect.currentframe().f_code.co_name
        last_id = -1
        #try:
        DEBUG_LOG(_fn_,"execute_sql",sql_query)
        #设置游标#
        cursor = self._mysqlcontext.cursor()
        # back返回成功影响的数据库条数#
        back = cursor.execute(sql_query)
        if need_last_id:
            last_id = self._mysqlcontext.insert_id()
            DEBUG_LOG(_fn_,"last_id=",str(last_id))
        cursor.close()
        self._mysqlcontext.commit()
        #更新server active_time
        self.active()
        if back==0:
            WARN_LOG(_fn_,"no sql result affected!!!!")
            return (1,last_id)
        elif back>0:
            DEBUG_LOG(_fn_,"success execute!!!!")
            return (0,last_id)

    #
    # 执行多条sql语句同时执行,除了select语句不接受外,写语句皆通过此函数执行
    # retnum:
    #    n: 返回受影响的行数   0:no affect    <0:something error
    #
    def executemany(self,sql_query,sql_args):
        _fn_ = inspect.currentframe().f_code.co_name
        try:
            DEBUG_LOG(_fn_,"start+++++++++++++++++++++++++++++++++")
            if self._mysqlcontext is None:
                ERROR_LOG(_fn_,":mysqlcontext is None")
                raise DM_MysqlError('mysqlcontext is None', _fn_)
            if not sql_query:
                WARN_LOG(_fn_,"sql query is None")
                raise DM_MysqlError('sql query is None', _fn_)
            DEBUG_LOG(_fn_,"execute_sql",sql_query)
            cursor = self._mysqlcontext.cursor()
            # back返回成功影响的数据库条数#
            back = cursor.executemany(sql_query, sql_args)
            # if need_last_id:
            #     last_id = self._mysqlcontext.insert_id()
            #     DEBUG_LOG(_fn_,"last_id=",str(last_id))
            #TODO:commit放到外面提交
            self._mysqlcontext.commit()
            cursor.close()
            #更新server active_time
            self.active()
            if back==0:
                WARN_LOG(_fn_,"no sql result affected!!!!")
                return last_id
            elif back>0:
                DEBUG_LOG(_fn_,"success execute!!!!")
                return last_id
        except Exception,e:
            ERROR_LOG(self._cn_,_fn_,":error",str(e))
            raise DM_MysqlError('sql begin filed', str(e), _fn_)

    #TODO:插入id获取有误????
    def get_last_insert_id(self):
        _fn_ = inspect.currentframe().f_code.co_name
        DEBUG_LOG(_fn_,"start+++++++++++++++++++++++++++++++++")
        if self._mysqlcontext is None:
            ERROR_LOG(_fn_,":mysqlcontext is None")
            raise DM_MysqlError('mysqlcontext is None', _fn_)
        try:
            return self._mysqlcontext.insert_id()
        except Exception,e:
            ERROR_LOG(self._cn_,_fn_,":error",str(e))
            raise DM_MysqlError('sql begin filed', str(e), _fn_)

    def begin_trans(self):
        _fn_ = inspect.currentframe().f_code.co_name
        if self._mysqlcontext is None:
            ERROR_LOG(_fn_,"mysqlcontext is None")
            raise DM_MysqlError('mysqlcontext is None', _fn_)
        if self._readonly or self._broken:
            ERROR_LOG(_fn_,"readonly server or server broken")
            raise DM_MysqlError('mysqlcontext is read only or broken', _fn_)
        try:
            self._mysqlcontext.begin()
            DEBUG_LOG(_fn_,":success!!!!")
        except Exception,e:
            ERROR_LOG(self._cn_,_fn_,":error",str(e))
            raise DM_MysqlError('sql begin filed', str(e), _fn_)

    def commit_trans(self):
        _fn_ = inspect.currentframe().f_code.co_name
        if self._mysqlcontext is None:
            ERROR_LOG(_fn_,"mysqlcontext is None")
            raise DM_MysqlError('mysqlcontext is None', _fn_)
        if self._readonly or self._broken:
            ERROR_LOG(_fn_,"readonly server or server broken")
            raise DM_MysqlError('mysqlcontext is read only or broken', _fn_)
        try:
            self._mysqlcontext.commit()
            DEBUG_LOG(_fn_,":success!!!!")
        except Exception,e:
            ERROR_LOG(self._cn_,_fn_,":error",str(e))
            raise DM_MysqlError('sql begin filed', str(e), _fn_)

    def rollback_trans(self):
        _fn_ = inspect.currentframe().f_code.co_name
        if self._mysqlcontext is None:
            ERROR_LOG(_fn_,"mysqlcontext is None")
            raise DM_MysqlError('mysqlcontext is None', _fn_)
        if self._readonly or self._broken:
            ERROR_LOG(_fn_,"readonly server or server broken")
            raise DM_MysqlError('mysqlcontext is read only or broken', _fn_)
        try:
            self._mysqlcontext.rollback()
            DEBUG_LOG(_fn_,":success!!!!")
        except Exception,e:
            ERROR_LOG(self._cn_,_fn_,":error",str(e))
            raise DM_MysqlError('sql begin filed', str(e), _fn_)



    def get_error_no(self):
        return self._mysqlcontext.errno()

    def __del__(self):
        if self._mysqlcontext:
            self._mysqlcontext.close()
            self._mysqlcontext = None



if __name__ == '__main__':
    _fn_="main"
    sqlconfig = "sql.config"
    logconfig = "log.config"

    try:
        # init common_log
        #if InitLog(logconfig)<0:
        #    print "logger init error"
        #    exit(-1)

        # sql_manager #
        s = PY_Mysql_Manager()
        if s.init(sqlconfig) < 0:
            DEBUG_LOG(_fn_,"sql config error")
            exit(-1)
        s.print_server()

        # sql case #
        c = s.get_db_case(True)
        if c is None:
            ERROR_LOG(_fn_,"get db case is None")
            exit(-1)

        sqlkey = " SELECT * from rule limit 5"
        back = c.query(sqlkey)
        print back

        sqlkey = " SELECT * from user limit 5"
        back = c.query(sqlkey)
        print back

        #不需要获取last_id方式
        rule = "hahahah"
        create_time = "2014-01-22 14:29:30"
        sqlkey = "insert into rule(rule,create_time) values('%s','%s')"%(rule,create_time)
        ret = c.query(sqlkey)
        DEBUG_LOG('End insert', ret)
        print ret

        #需要获取last_id方式
        rule = "neneneneen"
        create_time = "2014-01-22 15:29:30"
        sqlkey = "insert into rule(rule,create_time) values('%s','%s')"%(rule,create_time)
        last_id = c.query(sqlkey,True)
        if last_id < 0:
            ERROR_LOG(_fn_,"sql execute error")
            exit(-1)

        print last_id
    except Exception,e:
        print "main process error:%s"%(str(e))
        exit(-1)


